﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;

namespace ELO
{
    class Data
    {
        private string ConnectionString;

        public Data()
        {
            this.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        }

        public Player[] GetPlayers()
        {
            List<Player> result = new List<Player>();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT * FROM Players ORDER BY Name";
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        Player p = new Player();
                        p.ID = (long)reader["ID"];
                        p.Losses = (long)reader["Losses"];
                        p.Name = (string)reader["Name"];
                        p.Rating = (long)reader["Rating"];
                        p.Wins = (long)reader["Wins"];

                        result.Add(p);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return result.ToArray();
        }

        public Player GetPlayer(long playerID)
        {
            Player result = new Player();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT * FROM Players WHERE ID=@ID";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@ID", playerID));
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        result.ID = (long)reader["ID"];
                        result.Losses = (long)reader["Losses"];
                        result.Name = (string)reader["Name"];
                        result.Rating = (long)reader["Rating"];
                        result.Wins = (long)reader["Wins"];
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return result;
        }

        public void AddPlayer(Player p)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "INSERT INTO Players (Name, Wins, Losses, Rating) VALUES(@Name, @Wins, @Losses, @Rating)";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Name", p.Name));
                command.Parameters.Add(new SqlParameter("@Wins", p.Wins));
                command.Parameters.Add(new SqlParameter("@Losses", p.Losses));
                command.Parameters.Add(new SqlParameter("@Rating", p.Rating));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void DeletePlayer(long id)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "DELETE FROM Players WHERE ID=@ID";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@ID", id));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        public void AddGame(Game g)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "INSERT INTO Games (Date, Winner, Loser, TotalPoints, Gammon, Backgammon, TotalMoney) VALUES(@Date, @Winner, @Loser, @TotalPoints, @Gammon, @Backgammon, @TotalMoney)";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Date", g.GameDate.ToShortDateString()));
                command.Parameters.Add(new SqlParameter("@Winner", g.Winner));
                command.Parameters.Add(new SqlParameter("@Loser", g.Loser));
                command.Parameters.Add(new SqlParameter("@TotalPoints", g.TotalPoints));
                command.Parameters.Add(new SqlParameter("@Gammon", g.Gammon));
                command.Parameters.Add(new SqlParameter("@Backgammon", g.Backgammon));
                command.Parameters.Add(new SqlParameter("@TotalMoney", g.TotalMoney));
                connection.Open();
                command.ExecuteNonQuery();

                UpdateRatings(g);
                UpdateWinLoss(g.Winner, true);
                UpdateWinLoss(g.Loser, false);
            }
        }

        public void RecalculateAllRatings()
        {
            // set all the players back to 1500
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "UPDATE Players SET Rating=1500";
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                command.ExecuteNonQuery();
            }

            // now go through each of the games and recalculate based on the results of that game
            Game[] allGames = (from g in this.GetGames()
                               orderby g.GameDate ascending, g.ID ascending
                               select g).ToArray();
            foreach (Game g in allGames)
            {
                UpdateRatings(g);
            }
        }

        private void UpdateRatings(Game g)
        {
            // recalculate the ratings
            int newRatingA = 0;
            int newRatingB = 0;
            RecalculateRatings(g.Winner, g.Loser, out newRatingA, out newRatingB);
            UpdateRating(g.Winner, newRatingA);
            UpdateRating(g.Loser, newRatingB);
        }

        private void UpdateRating(long playerID, int rating)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "UPDATE Players SET Rating=@Rating WHERE ID=@ID";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Rating", rating));
                command.Parameters.Add(new SqlParameter("@ID", playerID));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        private void UpdateWinLoss(long playerID, bool won)
        {
            Player p = GetPlayer(playerID);
            long wins = p.Wins + (won ? 1 : 0);
            long losses = p.Losses + (won ? 0 : 1);
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "UPDATE Players SET Wins=@Wins, Losses=@Losses WHERE ID=@ID";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Wins", wins));
                command.Parameters.Add(new SqlParameter("@Losses", losses));
                command.Parameters.Add(new SqlParameter("@ID", playerID));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }

        private void RecalculateRatings(long playerA, long playerB, out int newRatingA, out int newRatingB)
        {
            // playerA is the winner
            // playerB is the loser

            // R'A = RA + K(SA-EA)
            // rating < 2100 gives K = 32
            // rating > 2400 gives K = 16
            // else K = 24

            double expectedScoreA = 0;
            double expectedScoreB = 0;
            GetExpectedScore(playerA, playerB, out expectedScoreA, out expectedScoreB);

            // player A
            int originalRating = GetRating(playerA);
            int k = 24;
            if (originalRating < 2100) k = 32;
            if (originalRating > 2400) k = 16;
            int s = 1;
            newRatingA = (int)(originalRating + k * (s - expectedScoreA));

            // player B
            originalRating = GetRating(playerB);
            k = 24;
            if (originalRating < 2100) k = 32;
            if (originalRating > 2400) k = 16;
            s = 0;
            newRatingB = (int)(originalRating + k * (s - expectedScoreB));
        }

        private int GetRating(long playerID)
        {
            int rating = 0;
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT Rating FROM Players WHERE ID=@ID";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@ID", playerID));
                connection.Open();
                rating = Int32.Parse(command.ExecuteScalar().ToString());
            }
            return rating;
        }

        private void GetExpectedScore(long playerA, long playerB, out double expectedA, out double expectedB)
        {
            int ratingA = GetRating(playerA);
            int ratingB = GetRating(playerB);

            expectedA = 1 / (1 + Math.Pow(10, ((ratingB - ratingA) / 400)));
            expectedB = 1 / (1 + Math.Pow(10, ((ratingA - ratingB) / 400)));
        }

        public double GetAmountOwed(long playerPayer, long playerPayee)
        {
            double owed = 0;
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT SUM(TotalMoney) FROM Games WHERE Winner=@Payee AND Loser=@Payer";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Payer", playerPayer));
                command.Parameters.Add(new SqlParameter("@Payee", playerPayee));
                connection.Open();
                string res = command.ExecuteScalar().ToString();
                if (!String.IsNullOrEmpty(res))
                {
                    owed = Double.Parse(res);
                }

                sql = "SELECT SUM(TotalMoney) FROM Games WHERE Winner=@Payer AND Loser=@Payee";
                command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Payer", playerPayer));
                command.Parameters.Add(new SqlParameter("@Payee", playerPayee));
                res = command.ExecuteScalar().ToString();
                if (!String.IsNullOrEmpty(res))
                {
                    owed -= Double.Parse(res);
                }

                sql = "SELECT SUM(Amount) FROM Payments WHERE [From]=@Payer AND [To]=@Payee";
                command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Payer", playerPayer));
                command.Parameters.Add(new SqlParameter("@Payee", playerPayee));
                res = command.ExecuteScalar().ToString();
                if (!String.IsNullOrEmpty(res))
                {
                    owed -= Double.Parse(res);
                }

                sql = "SELECT SUM(Amount) FROM Payments WHERE [From]=@Payee AND [To]=@Payer";
                command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Payer", playerPayer));
                command.Parameters.Add(new SqlParameter("@Payee", playerPayee));
                res = command.ExecuteScalar().ToString();
                if (!String.IsNullOrEmpty(res))
                {
                    owed += Double.Parse(res);
                }
            }

            if (owed < 0)
            {
                owed = 0;
            }
            return owed;
        }

        public void RecordPayment(long playerPayer, long playerPayee, double amount, DateTime transactionDate)
        {
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "INSERT INTO Payments([From], [To], Amount, TransactionDate) VALUES(@From, @To, @Amount, @TransactionDate)";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@From", playerPayer));
                command.Parameters.Add(new SqlParameter("@To", playerPayee));
                command.Parameters.Add(new SqlParameter("@Amount", amount));
                command.Parameters.Add(new SqlParameter("@TransactionDate", transactionDate));
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
        public int GetStreakForPlayer(long player)
        {
            int streak = 0;
            bool winStreak = true;
            bool firstLoop = true;
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT * FROM Games WHERE Winner=@Player OR Loser=@Player ORDER BY [DATE] DESC, [ID] DESC";
                SqlCommand command = new SqlCommand(sql, connection);
                command.Parameters.Add(new SqlParameter("@Player", player));
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        if (firstLoop)
                        {
                            // determine if this is a win streak or a losing streak
                            long winner = (long)reader["Winner"];
                            if (winner == player)
                            {
                                winStreak = true;
                            }
                            else
                            {
                                winStreak = false;
                            }
                            firstLoop = false;
                        }

                        if (winStreak)
                        {
                            if ((long)reader["Winner"] == player)
                            {
                                streak++;
                            }
                            else
                            {
                                break;
                            }
                        }
                        else
                        {
                            if ((long)reader["Loser"] == player)
                            {
                                streak--;
                            }
                            else
                            {
                                break;
                            }
                        }
                    }
                }
                finally
                {
                    reader.Close();
                }

                return streak;
            }
        }

        public Game[] GetLastTenGames()
        {
            List<Game> result = new List<Game>();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT TOP 10 * FROM Games ORDER BY [DATE] DESC, [ID] DESC";
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        Game g = new Game();
                        g.ID = (long)reader["ID"];
                        g.GameDate = DateTime.Parse(reader["Date"].ToString());
                        g.Winner = (long)reader["Winner"];
                        g.Loser = (long)reader["Loser"];
                        g.TotalPoints = (int)reader["TotalPoints"];
                        g.Gammon = (int)reader["Gammon"];
                        g.Backgammon = (int)reader["Backgammon"];
                        g.TotalMoney = (double)reader["TotalMoney"];

                        result.Add(g);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return result.ToArray();
        }

        public Game[] GetGames()
        {
            List<Game> result = new List<Game>();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                string sql = "SELECT * FROM Games ORDER BY [DATE] DESC, [ID] DESC";
                SqlCommand command = new SqlCommand(sql, connection);
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        Game g = new Game();
                        g.ID = (long)reader["ID"];
                        g.GameDate = DateTime.Parse(reader["Date"].ToString());
                        g.Winner = (long)reader["Winner"];
                        g.Loser = (long)reader["Loser"];
                        g.TotalPoints = (int)reader["TotalPoints"];
                        g.Gammon = (int)reader["Gammon"];
                        g.Backgammon = (int)reader["Backgammon"];
                        g.TotalMoney = (double)reader["TotalMoney"];

                        result.Add(g);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return result.ToArray();
        }

    }
}